package com.chen.mysqltopostgres.impl;

import com.chen.mysqltopostgres.SqlFileReader;
import com.chen.mysqltopostgres.constant.ColDataTypeMap;
import com.chen.mysqltopostgres.constant.Constant;
import com.chen.mysqltopostgres.util.SaveFileUtils;
import com.chen.mysqltopostgres.util.StatementUtils;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Sequence;
import net.sf.jsqlparser.statement.ReferentialAction.Action;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.alter.Alter;
import net.sf.jsqlparser.statement.alter.AlterExpression;
import net.sf.jsqlparser.statement.alter.AlterOperation;
import net.sf.jsqlparser.statement.comment.Comment;
import net.sf.jsqlparser.statement.create.sequence.CreateSequence;
import net.sf.jsqlparser.statement.create.table.ColDataType;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
import net.sf.jsqlparser.statement.create.table.Index;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.concurrent.atomic.AtomicBoolean;

/**
 * @author: cgd
 * @description:
 * @date: 2022/8/1 15:11
 * @version: 1.0
 */
public class MySqlFileReaderImpl implements SqlFileReader {
    private static final Logger log = LoggerFactory.getLogger(MySqlFileReaderImpl.class);

    /**
     * mysql 单行注释符（#）
     */
    public static final String MYSQL_SINGLE_LINE_COMMENT_ONE = "#";

    /**
     * mysql 单行注释符（--）
     */
    public static final String MYSQL_SINGLE_LINE_COMMENT_TWO = "--";

    /**
     * mysql 多行注释起始符(/*)
     */
    public static final String MYSQL_MULTILINE_COMMENT_STARTER_ONE = "/*";

    /**
     * mysql 多行注释起始符(/*!)
     */
    public static final String MYSQL_MULTILINE_COMMENT_STARTER_TWO = "/*!";

    /**
     * mysql 多行注释终止符
     */
    public static final String MYSQL_MULTILINE_COMMENT_TERMINATOR = "*/";

    /**
     * mysql 语句结束符
     */
    public static final String STATEMENT_TERMINATOR = ";";

    /**
     * 保存去除注释的文件路径
     */
    private String noCommentFilePath;

    /**
     * 保存转换后的文件路径
     */
    private final String pgsqlFilePath;

    public MySqlFileReaderImpl(String pgsqlFilePath) {
        this.pgsqlFilePath = pgsqlFilePath;
    }

    public MySqlFileReaderImpl(String pgsqlFilePath, String noCommentFilePath) {
        this.pgsqlFilePath = pgsqlFilePath;
        this.noCommentFilePath = noCommentFilePath;
    }

    @Override
    public void read(String filePath) throws IOException {
        File file = new File(filePath);
        BufferedReader br = new BufferedReader(new FileReader(file));

        // 注释开始标识
        boolean startAnnotation = false;

        List<String> sqlString = new ArrayList<>();
        String lineString;
        while ((lineString = br.readLine()) != null) {
            //1.判断是否为注释
            //2.判断是否为可处理语句
            //3.是否为完整语句
            //4.处理完整语句

            if (StringUtils.isBlank(lineString)) {
                continue;
            }

            // 单行注释去除
            int commentStartIndex;
            if ((commentStartIndex = lineString.indexOf(MYSQL_SINGLE_LINE_COMMENT_ONE)) >= 0
                    || (commentStartIndex = lineString.indexOf(MYSQL_SINGLE_LINE_COMMENT_TWO)) >= 0) {
                lineString = lineString.substring(0, commentStartIndex);
                if (StringUtils.isBlank(lineString) || STATEMENT_TERMINATOR.equals(lineString)) {
                    continue;
                }
            }

            // 多行注释去除
            startAnnotation = removeMultiLineComments(lineString, startAnnotation, sqlString);

            // 获取单条SQL
            String singleStatement = getSingleStatement(sqlString);
            // 解析SQL
            if (StringUtils.isNotEmpty(singleStatement)) {
                parseStatement(singleStatement);
            }
        }
    }

    /**
     * 解析语句
     */
    private void parseStatement(String statement) throws IOException {
        // 去除pgsql不识别字符
        statement = statement.replace("`", "");
        Statement parse = null;
        try {
            parse = CCJSqlParserUtil.parse(statement);
        } catch (Exception e) {
            log.error("sql statement parsing failed：", e);
        }

        if (parse instanceof CreateTable) {
            createTableStatementHandler((CreateTable) parse);
        }
    }

    /**
     * 处理创建语句
     */
    private void createTableStatementHandler(CreateTable stmt) throws IOException {
        List<String> saveSql = new ArrayList<>();
        //表选项处理
        tableOptionsStringsHandler(stmt, saveSql);
        //列定义处理
        columnDefinitionsHandler(stmt, saveSql);
        //索引处理
        indexHandler(stmt, saveSql);

        String formatSql = StatementUtils.formatSql(stmt + STATEMENT_TERMINATOR);
        saveSql.add(formatSql);
        // 将创建语句放到第一
        Collections.reverse(saveSql);
        saveSql.add(Constant.SEPARATOR);
        SaveFileUtils.saveSql(saveSql, pgsqlFilePath);
    }

    /**
     * 索引处理
     */
    private void indexHandler(CreateTable stmt, List<String> saveSql) {
        List<Index> indexes = stmt.getIndexes();
        if (CollectionUtils.isEmpty(indexes)) {
            return;
        }
        indexes.removeIf(index -> {
            index.setIndexSpec(null);
            String columnsNames = StringUtils.join(index.getColumnsNames(), "_");
            if (Constant.PRIMARY_KEY.equals(index.getType())) {
                index.setName(stmt.getTable().getName().toUpperCase() + "_" + columnsNames + "_PK");
            } else if (Constant.UNIQUE_KEY.equals(index.getType())) {
                index.setType(Constant.CREATE_UNIQUE_INDEX);
                index.setName(stmt.getTable().getName().toUpperCase() + "_" + columnsNames + "_IDX");
                saveSql.add(StatementUtils.pgsqlIndexFill(index, stmt.getTable().getName()));
                return true;
            } else if (Constant.KEY.equals(index.getType())) {
                index.setType(Constant.CREATE_INDEX);
                index.setName(stmt.getTable().getName().toUpperCase() + "_" + columnsNames + "_IDX");
                saveSql.add(StatementUtils.pgsqlIndexFill(index, stmt.getTable().getName()));
                return true;
            }
            return false;
        });
    }

    /**
     * 列定义处理
     */
    private void columnDefinitionsHandler(CreateTable stmt, List<String> saveSql) {
        List<ColumnDefinition> columnDefinitions = stmt.getColumnDefinitions();
        int indexOf;
        for (ColumnDefinition columnDefinition : columnDefinitions) {
            // 映射dataType
            ColDataType colDataType = columnDefinition.getColDataType();
            String dataType = colDataType.getDataType();
            String newDataType = ColDataTypeMap.dataTypeMap.get(dataType.toLowerCase());
            colDataType.setDataType(StringUtils.defaultString(newDataType, dataType));
            colDataType.setCharacterSet(null);
            if ("smallint".equals(newDataType)) {
                colDataType.setArgumentsStringList(null);
            }

            List<String> columnSpecs = columnDefinition.getColumnSpecs();
            // 添加column comment
            if ((indexOf = columnSpecs.indexOf(Constant.COMMENT)) > -1
                    || (indexOf = columnSpecs.indexOf(Constant.COMMENT.toLowerCase())) > -1) {
                Comment comment = new Comment();
                comment.setComment(new StringValue(columnSpecs.get(indexOf + 1)));
                comment.setColumn(new Column(stmt.getTable(), columnDefinition.getColumnName()));
                saveSql.add(comment + STATEMENT_TERMINATOR);
                log.debug("column comment:{}", comment);
            }
            // 添加自增序列
            if (columnSpecs.contains(Constant.MYSQL_AUTO_INCREMENT.toLowerCase())
                    || columnSpecs.contains(Constant.MYSQL_AUTO_INCREMENT)) {
                createSequence(columnDefinition, stmt, saveSql);
                log.debug("add pgsql auto increment");
            }
            // 允许清除标识
            AtomicBoolean delFlag = new AtomicBoolean(true);
            // 清除多余的内容
            columnSpecs.removeIf(next -> {
                boolean result = !Constant.PG_RESERVED_WORD.contains(next.toUpperCase()) && delFlag.get();
                delFlag.set(!Constant.DEFAULT.equalsIgnoreCase(next));
                return result;
            });
        }
    }

    /**
     * 创建pgsql序列
     */
    private void createSequence(ColumnDefinition columnDefinition, CreateTable stmt, List<String> saveSql) {
        Sequence sequence = new Sequence();
        String sequenceName = stmt.getTable().getName() + "_" + columnDefinition.getColumnName() + "_seq";
        sequence.setName(sequenceName);
        sequence.setSchemaName("IF NOT EXISTS public");
        Sequence.Parameter incrementBy = new Sequence.Parameter(Sequence.ParameterType.INCREMENT_BY);
        incrementBy.setValue(1L);
        Sequence.Parameter restartWith = new Sequence.Parameter(Sequence.ParameterType.RESTART_WITH);
        restartWith.setValue(1L);
        Sequence.Parameter cache = new Sequence.Parameter(Sequence.ParameterType.CACHE);
        cache.setValue(1L);
        sequence.setParameters(Arrays.asList(incrementBy, restartWith, cache));

        CreateSequence createSequence = new CreateSequence();
        createSequence.setSequence(sequence);

        Alter alter = new Alter();
        alter.setTable(stmt.getTable());
        AlterExpression alterExpression = new AlterExpression();
        alterExpression.hasColumn(true);
        alterExpression.setOperation(AlterOperation.ALTER);
        alterExpression.addParameters(Action.SET_DEFAULT.getAction()
                , "nextval('public." + sequence.getName() + "'::regclass)");
        alterExpression.setColumnName(columnDefinition.getColumnName());

        alter.setAlterExpressions(List.of(alterExpression));
        saveSql.add(alter + STATEMENT_TERMINATOR);
        saveSql.add(createSequence + STATEMENT_TERMINATOR);
    }

    /**
     * 表选项处理
     */
    private void tableOptionsStringsHandler(CreateTable stmt, List<String> saveSql) {
        List<String> tableOptionsStrings = stmt.getTableOptionsStrings();
        int indexOf;
        if ((indexOf = tableOptionsStrings.indexOf(Constant.COMMENT)) > -1
                || (indexOf = tableOptionsStrings.indexOf(Constant.COMMENT.toLowerCase())) > -1) {
            Comment comment = new Comment();
            comment.setTable(stmt.getTable());
            comment.setComment(new StringValue(tableOptionsStrings.get(indexOf + 2)));
            saveSql.add(comment + STATEMENT_TERMINATOR);
            log.debug("table comment:{}", comment);
        }
        stmt.setIfNotExists(true);
        // pgsql 不需要改参数
        tableOptionsStrings.clear();
    }

    /**
     * 获取单条语句
     */
    private String getSingleStatement(List<String> sqlString) throws IOException {
        String statement = "";
        if (CollectionUtils.isEmpty(sqlString) || !sqlString.get(sqlString.size() - 1).contains(STATEMENT_TERMINATOR)) {
            return statement;
        }
        statement = StringUtils.join(sqlString, Constant.SEPARATOR);
        String[] split = statement.split(STATEMENT_TERMINATOR);

        // 保存去除注释的SQL
        if (StringUtils.isNotEmpty(noCommentFilePath)) {
            SaveFileUtils.saveSql(sqlString, noCommentFilePath);
        }

        // 清空List
        sqlString.clear();
        // 结束符后还有内容
        if (split.length > 1) {
            sqlString.add(split[1]);
        }
        return split[0];
    }

    /**
     * 去除多行注释
     *
     * @param lineString 单行数据
     */
    private boolean removeMultiLineComments(String lineString, Boolean startAnnotation, List<String> sql) {
        if (startAnnotation) {
            int commentEndIndex;
            if ((commentEndIndex = lineString.indexOf(MYSQL_MULTILINE_COMMENT_TERMINATOR)) > -1) {
                lineString = lineString.substring(commentEndIndex + 2).trim();
                if (lineString.startsWith(STATEMENT_TERMINATOR)) {
                    lineString = lineString.substring(1).trim();
                }
                if (StringUtils.isNotBlank(lineString) && !STATEMENT_TERMINATOR.equals(lineString)) {
                    return removeMultiLineComments(lineString, false, sql);
                } else {
                    return false;
                }
            } else {
                return true;
            }
        } else {
            int commentStartIndex;
            if ((commentStartIndex = lineString.indexOf(MYSQL_MULTILINE_COMMENT_STARTER_ONE)) > -1
                    || (commentStartIndex = lineString.indexOf(MYSQL_MULTILINE_COMMENT_STARTER_TWO)) > -1) {
                int commentEndIndex;
                if ((commentEndIndex = lineString.indexOf(MYSQL_MULTILINE_COMMENT_TERMINATOR)) > -1) {
                    lineString = lineString.substring(0, commentStartIndex).trim() + lineString.substring(commentEndIndex + 2);
                    if (lineString.startsWith(STATEMENT_TERMINATOR)) {
                        lineString = lineString.substring(1).trim();
                    }
                    if (StringUtils.isNotBlank(lineString) && !STATEMENT_TERMINATOR.equals(lineString)) {
                        removeMultiLineComments(lineString, false, sql);
                    }
                } else {
                    lineString = lineString.substring(0, commentStartIndex).trim();
                    if (StringUtils.isNotBlank(lineString) && !STATEMENT_TERMINATOR.equals(lineString)) {
                        sql.add(lineString);
                    }
                    return true;
                }
            } else {
                sql.add(lineString);
            }
        }
        return false;
    }
}